/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author Thai Dac Vinh
 */
public class LeagueDAO {
    // JDBC variables
    DataSource ds = null;
    
    public LeagueDAO() {
        try {
            Context ctx = new InitialContext();
            ds = (DataSource) ctx.lookup("java:comp/env/jdbc/leagueDataSource");
        } catch (Exception e) {
            System.err.println(e);
        }
    }
    
    public List getAllLeagues() {
        List leagues = new LinkedList();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM \"League\"";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("LID");
                int year = rs.getInt("year");
                String season = rs.getString("season");
                String title = rs.getString("title");
                League league = new League(id, year, season, title);
                leagues.add(league);
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return leagues;
    }
    
    public int insert (int year, String season, String title) {
        try {
            Connection conn = ds.getConnection();
            String sql = "INSERT INTO \"League\"(\"year\",\"season\",\"title\") VALUES(?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, year);
            pstmt.setString(2, season);
            pstmt.setString(3, title);
            if (pstmt.executeUpdate() > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                rs.next();
                return rs.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return -1;
    }
}
